﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_F4Search : UserControl
    {

        private INISettings mySetting;
        private SqlConnection myConnection;

        public panel_F4Search()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            LoadData();

            this.searchValue.LostFocus += searchValue_LostFocus;
        }

        void searchValue_LostFocus(object sender, EventArgs e)
        {
            for (int i = 0; i < this.searchValue.Properties.Items.Count; i++)
            {
                ComboboxItem item = (ComboboxItem)this.searchValue.Properties.Items[i];
                if (item.Value.ToString().ToUpper() == this.searchValue.Text.Trim().ToUpper())
                {
                    this.searchValue.SelectedIndex = i;
                }
            }
        }


        //////////////////////////////////////////////
        /// Load Data to Search Form
        //////////////////////////////////////////////
        private void LoadData()
        {
            ComboboxItem item = new ComboboxItem();
            item.Text = "Product Name";
            item.Value = "1";
            searchMode.Properties.Items.Add(item);

            ComboboxItem item2 = new ComboboxItem();
            item2.Text = "Serial Number";
            item2.Value = "2";
            searchMode.Properties.Items.Add(item2);

            ComboboxItem item3 = new ComboboxItem();
            item3.Text = "Order Number";
            item3.Value = "3";
            searchMode.Properties.Items.Add(item3);

            ComboboxItem item4 = new ComboboxItem();
            item4.Text = "Plan Number";
            item4.Value = "4";
            searchMode.Properties.Items.Add(item4);

            searchMode.SelectedIndex = 0;
            searchExten.SelectedIndex = 0;            
        }

        private void searchMode_SelectedIndexChanged(object sender, EventArgs e)
        {
            switch (searchMode.SelectedIndex)
            {
                case 0:
                    searchValue.Properties.Items.Clear();
                    searchExten.Enabled = false;
                    searchExten.SelectedIndex = 0;
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT CompID FROM [231_TimePlan] GROUP BY CompID ORDER BY CompID";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        while (dataReader.Read())
                        {
                            ComboboxItem item = new ComboboxItem();
                            item.Text = dataReader["CompID"].ToString().Trim();
                            item.Value = dataReader["CompID"].ToString().Trim();
                            searchValue.Properties.Items.Add(item);
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show("Cannot get Comp list.");
                    }
                    searchValue.SelectedIndex = 0;
                    break;
                case 1:
                    searchExten.Enabled = false;
                    searchValue.Properties.Items.Clear();
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT DISTINCT SerialNo"
                            + " FROM [231_RobotOrderDetail]"
                            + " WHERE (FinishedDate IS NULL)"
                            + " OR (FinishedDate > DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) - 900)"
                            + " ORDER BY SerialNo";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        while (dataReader.Read())
                        {
                            ComboboxItem item = new ComboboxItem();
                            item.Text = dataReader["SerialNo"].ToString();
                            item.Value = dataReader["SerialNo"].ToString();
                            searchValue.Properties.Items.Add(item);
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show("Cannot get SerialNo list.");
                    }
                    searchValue.SelectedIndex = 0;
                    break;
                case 2:
                    searchExten.Enabled = true;
                    searchExten.SelectedIndex = 0;
                    searchValue.Properties.Items.Clear();
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT TimePlanNo FROM [231_TimePlan] ORDER BY TimePlanNo";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        while (dataReader.Read())
                        {
                            ComboboxItem item = new ComboboxItem();
                            item.Text = dataReader["TimePlanNo"].ToString();
                            item.Value = dataReader["TimePlanNo"].ToString();
                            searchValue.Properties.Items.Add(item);
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show("Cannot get Order No list.");
                    }
                    searchValue.SelectedIndex = 0;
                    break;
                case 3:
                    searchExten.Enabled = false;
                    searchValue.Properties.Items.Clear();
                    try
                    {
                        myConnection.Open();
                        SqlCommand cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "SELECT DISTINCT PlanNo FROM [231_TimePlan] WHERE (PlanNo IS NOT NULL) ORDER BY PlanNo";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        while (dataReader.Read())
                        {
                            ComboboxItem item = new ComboboxItem();
                            item.Text = dataReader["PlanNo"].ToString();
                            item.Value = dataReader["PlanNo"].ToString();
                            searchValue.Properties.Items.Add(item);
                        }
                        dataReader.Close();
                        cmd_sql.Dispose();
                        myConnection.Close();
                    }
                    catch
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show("Cannot get Plan No list.");
                    }
                    searchValue.SelectedIndex = 0;
                    break;
            }            
        }

        private void buttonSearch_Click(object sender, EventArgs e)
        {
            string sql = null;
            
            gridSearchResult.DataSource = null;
            SearchResult.Columns.Clear();

            if (this.searchValue.SelectedIndex >= 0)
            {
                ComboboxItem item = new ComboboxItem();
                item = (ComboboxItem)this.searchValue.SelectedItem;
                switch (searchMode.SelectedIndex)
                {
                    case 0:
                        sql = "SELECT [231_TimePlan].PlanNo, [231_TimePlan].TimePlanNo, [231_TimePlan].CompID,"
                            + " [231_TimePlan].Quantity, [231_TimePlan].SubOrder1, [231_TimePlan].FromSerialNo,"
                            + " [231_TimePlan].ToSerialNo, [231_TimePlan].DeadLine, [222_Customer].FullName,"
                            + " [231_TimePlan].FinishedDate, [231_TimePlan].Remark"
                            + " FROM [231_TimePlan] LEFT OUTER JOIN"
                            + " [222_Customer] ON [231_TimePlan].CustomerID=[222_Customer].CustomerID"
                            + " WHERE  (CompID LIKE '%" + item.Value.ToString() + "%')";
                        break;
                    case 1:
                        sql = "SELECT [231_RobotOrderDetail].SerialNo, [231_RobotOrderDetail].PlanNo,"
                            + " [231_RobotOrderDetail].OrderNo, [231_RobotOrderDetail].RepairOrder, [231_RobotOrderDetail].CompID,"
                            + " [231_RobotOrderDetail].RepairComp, [231_RobotOrderDetail].Quantity,"
                            + " [231_RobotOrderDetail].ExportedDate, [222_Customer].FullName, [231_RobotOrderDetail].RoHS,"
                            + " [231_RobotOrderDetail].Remark, [231_RobotOrderDetail].RobotSerialNo"
                            + " FROM [231_RobotOrderDetail] LEFT OUTER JOIN"
                            + " [222_Customer] ON [222_Customer].CustomerID=[231_RobotOrderDetail].CustomerID"
                            + " WHERE ([231_RobotOrderDetail].SerialNo LIKE '%" + item.Value.ToString() + "%')";
                        break;
                    case 2:
                        if (searchExten.SelectedIndex == 0)
                        {
                            sql = "SELECT [231_RobotOrderDetail].SerialNo, [231_RobotOrderDetail].PlanNo,"
                                + " [231_RobotOrderDetail].OrderNo, [231_RobotOrderDetail].RepairOrder,"
                                + " [231_RobotOrderDetail].CompID, [231_RobotOrderDetail].RepairComp, [231_RobotOrderDetail].Quantity,"
                                + " [231_RobotOrderDetail].ExportedDate, [222_Customer].FullName, [231_RobotOrderDetail].Remark"
                                + " FROM [231_RobotOrderDetail] LEFT OUTER JOIN"
                                + " [222_Customer] ON [222_Customer].CustomerID=[231_RobotOrderDetail].CustomerID"
                                + " WHERE ([231_RobotOrderDetail].OrderNo LIKE '%" + item.Value.ToString() + "%')";
                        }
                        else
                        {
                            sql = "SELECT [231_TimePlan].PlanNo, [231_TimePlan].TimePlanNo, [231_TimePlan].SubOrder6,"
                                + " [231_TimePlan].CompID, [231_TimePlan].NewComp, [231_TimePlan].Quantity,"
                                + " [231_TimePlan].Remark, [231_TimePlan].FromSerialNo, [231_TimePlan].ToSerialNo,"
                                + " [231_TimePlan].DeadLine, [231_TimePlan].FinishedDate"
                                + " FROM [231_TimePlan] WHERE (TimePlanNo LIKE '%" + item.Value.ToString() + "%')";
                        }
                        break;
                    case 3:
                        sql = "SELECT [231_TimePlan].PlanNo, [231_TimePlan].TimePlanNo, [231_TimePlan].SubOrder6, [231_TimePlan].CompID,"
                            + " [231_TimePlan].NewComp, [231_TimePlan].Quantity, [231_TimePlan].Remark, [231_TimePlan].FromSerialNo,"
                            + " [231_TimePlan].ToSerialNo, [231_TimePlan].DeadLine, [231_TimePlan].FinishedDate"
                            + " FROM [231_TimePlan] WHERE ([231_TimePlan].PlanNo LIKE '%" + item.Value.ToString() + "%')";
                        break;
                }
            }

            if (string.IsNullOrEmpty(sql))
            {
                MessageBox.Show("No data to Search");
            }
            else
            {                                
                try
                {
                    DataSet sourceDataSet = new DataSet();
                    myConnection.Open();                    
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);                    
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd_sql;
                    adapter.Fill(sourceDataSet);
                    adapter.Dispose();
                    cmd_sql.Dispose();
                    myConnection.Close();

                    if (sourceDataSet != null)
                    {
                        if (sourceDataSet.Tables[0].Rows.Count > 0)
                        {
                            gridSearchResult.DataSource = sourceDataSet.Tables[0];
                            //MessageBox.Show("Found " + sourceDataSet.Tables[0].Rows.Count + " results");
                        }
                        else
                        {
                            MessageBox.Show("Not Found!");
                        }
                    }
                    else
                    {
                        MessageBox.Show("Not Found!");
                    }
                    SearchResult.BestFitColumns();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Not Found!");
                }
            }
        }
    }
}
